package com.ld.shieldsb.db.dao.dynamic;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.RowSetDynaClass;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import com.alibaba.druid.pool.DruidDataSource;
import com.ld.shieldsb.common.core.collections.ListUtils;
import com.ld.shieldsb.common.core.util.StackTraceUtil;
import com.ld.shieldsb.dao.BasicDao;
import com.ld.shieldsb.dao.DataSourceFactory;
import com.ld.shieldsb.dao.TransactionManager;
import com.ld.shieldsb.dao.model.PageNavigationBean;
import com.ld.shieldsb.dao.model.QueryModel;

import lombok.extern.slf4j.Slf4j;

/**
 * 动态DAO用于数据源为动态参数时用,或者不知道model类型时用,
 * 
 * @ClassName DynamicBaseDao
 * @author <a href="mailto:donggongai@126.com" target="_blank">吕凯</a>
 * @date 2018年2月3日 上午10:22:11
 *
 */
@Slf4j
public class DynamicBaseDao extends BasicDao {

    protected ThreadLocal<Connection> CONNECTION_THREAD_LOCAL;
    protected DruidDataSource DATA_SOURCE;

    public static final String PROP_CONNECTIONERRORRETRYATTEMPTS = "connectionErrorRetryAttempts";
    public static final String PROP_BREAKAFTERACQUIREFAILURE = "breakAfterAcquireFailure";
    public static final String PROP_TIMEBETWEENCONNECTERRORMILLIS = "timeBetweenConnectErrorMillis";

    protected final static QueryRunner QRY_RUN = new QueryRunner();

    public DynamicBaseDao(String url, String userName, String password) {
        this(url, userName, password, null);
    }

    public DynamicBaseDao(String url, String userName, String password, Map<String, String> exitenProperties) {
        super();
        Map<String, String> sourceMap = new HashMap<>();
        sourceMap.put("url", url);
        sourceMap.put("username", userName);
        sourceMap.put("password", password);
        sourceMap.put("removeAbandoned", "true"); // 超过时间限制是否回收
        sourceMap.put("removeAbandonedTimeout", "1800"); // 超时时间；单位为秒。1800秒=30分钟
        sourceMap.put("logAbandoned", "true"); // 关闭abanded连接时输出错误日志
        sourceMap.put("maxWait", "60000"); // 配置获取连接等待超时的时间，在此时间内获取不到会一直尝试

//        sourceMap.put("initialSize", "1"); // 配置获取连接等待超时的时间
//        sourceMap.put("minIdle", "1"); // 配置获取连接等待超时的时间
//        sourceMap.put("maxActive", "20"); // 配置获取连接等待超时的时间
//        sourceMap.put("timeBetweenEvictionRunsMillis", "60000"); // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
//        sourceMap.put("minEvictableIdleTimeMillis", "300000"); // 配置一个连接在池中最小生存的时间，单位是毫秒
//        sourceMap.put("poolPreparedStatements", "true"); // 打开PSCache，并且指定每个连接上PSCache的大小
//        sourceMap.put("maxPoolPreparedStatementPerConnectionSize", "20"); //
//        sourceMap.put("filters", "stat,mergeStat"); // 配置监控统计拦截的filters
//        sourceMap.put("connectionProperties", "druid.stat.slowSqlMillis=1000"); // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
//        sourceMap.put("removeAbandoned", "true"); // 对于长时间不使用的连接强制关闭
//        sourceMap.put("removeAbandonedTimeout", "1800"); // 超过30分钟开始关闭空闲连接 (单位秒)
//        sourceMap.put("logAbandoned", "true"); // 将当前关闭动作记录到日志

        if (exitenProperties != null) {
            sourceMap.putAll(exitenProperties);
        }

//        sourceMap.put("connectionErrorRetryAttempts", "3");
//        sourceMap.put("breakAfterAcquireFailure", "true"); 
        DATA_SOURCE = (DruidDataSource) DataSourceFactory.getDataSource(sourceMap);
//        DATA_SOURCE.setConnectionErrorRetryAttempts(1); // 连接出错时重试次数
//        DATA_SOURCE.setBreakAfterAcquireFailure(true); // 连接出错时重试N次后是否退出，需要退出的可以通过exitenProperties配置
        DATA_SOURCE.setTimeBetweenConnectErrorMillis(300000); // 连接出错后重试时间间隔，5分钟
        if (sourceMap.get(sourceMap.get(PROP_CONNECTIONERRORRETRYATTEMPTS)) != null) {
            DATA_SOURCE.setConnectionErrorRetryAttempts(Integer.parseInt(sourceMap.get(PROP_CONNECTIONERRORRETRYATTEMPTS))); // 连接出错时重试次数
        }
        if (sourceMap.get(sourceMap.get(PROP_BREAKAFTERACQUIREFAILURE)) != null) {
            DATA_SOURCE.setBreakAfterAcquireFailure(Boolean.parseBoolean(sourceMap.get(PROP_BREAKAFTERACQUIREFAILURE))); // 连接出错时重试N次后是否退出，需要退出的可以通过exitenProperties配置
        }
        if (sourceMap.get(sourceMap.get(PROP_TIMEBETWEENCONNECTERRORMILLIS)) != null) {
            DATA_SOURCE.setTimeBetweenConnectErrorMillis(Long.parseLong(sourceMap.get(PROP_TIMEBETWEENCONNECTERRORMILLIS))); // 连接出错后重试时间间隔，5分钟
        }

        CONNECTION_THREAD_LOCAL = DataSourceFactory.getThreadLocalConnection(url, userName);
        exceptionThreadLocal = DataSourceFactory.getThreadLocalException(url, userName);
        canSave = true;
        canRead = true;
    }

    /**
     * 是否可用
     * 
     * @Title isEnable
     * @author 吕凯
     * @date 2018年2月3日 下午2:32:26
     * @return boolean
     */
    public final boolean isEnable() {
        return DATA_SOURCE.isEnable();
    }

    private ResultSetHandler<DynaBean> objectRsHandler = new ResultSetHandler<DynaBean>() {
        public DynaBean handle(ResultSet rs) throws SQLException {
            RowSetDynaClass rsdc = new RowSetDynaClass(rs);
            if (ListUtils.isNotEmpty(rsdc.getRows())) {
                return rsdc.getRows().get(0);
            }
            return null;
        }
    };

    private ResultSetHandler<List<DynaBean>> listHandler = new ResultSetHandler<List<DynaBean>>() {

        @Override
        public List<DynaBean> handle(ResultSet rs) throws SQLException {
            List<DynaBean> objectList = new ArrayList<>();
            RowSetDynaClass rsdc = new RowSetDynaClass(rs);
            for (int i = 0; i < rsdc.getRows().size(); i++) {
                objectList.add(rsdc.getRows().get(i));
            }
            return objectList;
        }
    };

    /**
     * 
     * 根据查询条件返回实体bean
     * 
     * @Title getOne
     * @author 武杨
     * @date 2022年4月6日 上午9:23:49
     * @param <T>
     * @param classOfT
     * @param queryModel
     * @return T
     */
    public <T> T getOne(Class<T> classOfT, QueryModel queryModel) {
        String tableName = getTableName(classOfT);
        String sql = "SELECT " + queryModel.getSelectFields() + " FROM " + tableName + " " + queryModel.getOrderQueryStr() + " limit 1";
        return getOne(classOfT, sql, queryModel.getParams());
    }

    /**
     * 
     * 根据SQL动态获取bean
     * 
     * @Title getObject
     * @param sql
     *            sql
     * @param params
     *            查询条件
     * @return DynaBean
     */
    public final DynaBean getOne(String sql, Object... params) {
        try {
            Connection connection = getCon();
            if (!connection.getAutoCommit() && exceptionThreadLocal.get() != null) {
                log.warn("检测到事务中存在异常(当前sql：" + sql + ")查询操作将不执行", exceptionThreadLocal.get());
                return null;
            }
            try {
                return QRY_RUN.query(connection, sql, objectRsHandler, params);
            } finally {
                if (connection.getAutoCommit()) {
                    closeCon(connection);
                }
            }
        } catch (Exception e) {
            setException(e);
            log.error("查询出错,sql:" + sql, e);
            return null;
        }
    }

    /**
     * 
     * 根据SQL动态获取bean 捕捉异常的
     * 
     * @Title getObject
     * @param sql
     *            sql
     * @param params
     *            查询条件
     * @return DynaBean
     * @throws Exception
     */
    public final DynaBean getOneSql(String sql, Object... params) throws Exception {
        try {
            Connection connection = getCon();
            if (!connection.getAutoCommit() && exceptionThreadLocal.get() != null) {
                log.warn("检测到事务中存在异常(当前sql：" + sql + ")查询操作将不执行", exceptionThreadLocal.get());
                return null;
            }
            try {
                return QRY_RUN.query(connection, sql, objectRsHandler, params);
            } finally {
                if (connection.getAutoCommit()) {
                    closeCon(connection);
                }
            }
        } catch (Exception e) {
            exceptionThreadLocal.set(e);
            log.error("是否为正确的sql出错:" + sql, e);
            if (e.toString().contains("Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp")) {
                log.error("是否为正确的sql出错:" + e.toString());
                setException(e);
                throw new Exception("databaseError", e);
            } else {
                setException(e);
                throw new Exception("非正确的sql", e);
            }
        }
    }

    /**
     *
     * 根据查询条件返回具体条数的list
     *
     * @Title getList
     * @param queryModel
     *            查询条件
     * @param size
     *            条数
     * @return List<T>
     */
    public <T> List<T> getList(Class<T> classOfT, QueryModel queryModel, int size) {
        String tableName = getTableName(classOfT);
        String sql = "SELECT " + queryModel.getSelectFields() + " FROM " + tableName + " " + queryModel.getNoOrderQueryStr(null) + " LIMIT "
                + size;
        return getList(classOfT, sql, queryModel.getParams());
    }

    /**
     *
     * 数据库是否存在某给字段值
     *
     * @Title exists
     * @param key
     *            数据库字段
     * @param value
     *            字段值
     * @return boolean
     */
    public <T> boolean exists(Class<T> classOfT, String key, Object value) {
        String tableName = getTableName(classOfT);
        return getCount("SELECT 1 FROM " + tableName + " WHERE " + key + "=? LIMIT 1", value) > 0;
    }

    /**
     * 
     * 根据SQL动态获取LIST
     * 
     * @Title getList
     * @param sql
     *            sql
     * @param params
     *            条件
     * @return List<DynaBean>
     */
    public final List<DynaBean> getList(String sql, Object... params) {
        try {
            Connection connection = getCon();
            try {
                return QRY_RUN.query(connection, sql, listHandler, params);
            } finally {
                if (connection.getAutoCommit()) {
                    closeCon(connection);
                }
            }
        } catch (Exception e) {
            setException(e);
            log.error("查询出错,sql:" + sql, e);
            return new ArrayList<>();
        }
    }

    /**
     * 获取分页数据,这是oracle的
     * 
     * @Title getPageNavigationBean
     * @param sql
     *            查询sql
     * @param pageNum
     *            页码
     * @param pageSize
     *            条数
     * @param params
     *            查询条件
     * @return PageNavigationBean<DynaBean>
     */
    public PageNavigationBean<DynaBean> getPageNavigationBean(String sql, int pageNum, int pageSize, Object... params) {
        PageNavigationBean<DynaBean> pageBean = new PageNavigationBean<>();
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);
        String countSql = "select count(1) from (" + sql + ")";
        int count = getCount(countSql, params);

        pageBean.setTotalCount(count);

        int startRowNum = pageBean.getCurrentPoint();
        int endRowNum = startRowNum + pageSize;

        sql = "SELECT * FROM (SELECT TT.*, ROWNUM AS ROWNO FROM (" + sql + ") TT WHERE ROWNUM <" + endRowNum
                + ") TABLE_ALIAS where TABLE_ALIAS.rowno >=" + startRowNum;
        List<DynaBean> list = getList(sql, params);
        pageBean.setResultList(list);
        return pageBean;
    }

    /**
     *
     * 返回分页数据，这是mysql的
     *
     * @Title getPageNavigationBean
     * @param queryModel
     *            查询条件
     * @param pageNum
     *            页码
     * @param pageSize
     *            条数
     * @return PageNavigationBean<T>
     */
    public <T> PageNavigationBean<T> getPageNavigationBean(Class<T> classOfT, QueryModel queryModel, int pageNum, int pageSize) {
        PageNavigationBean<T> pageBean = new PageNavigationBean<>();
        pageBean.setCurrentPage(pageNum);
        pageBean.setPageSize(pageSize);
        String tableName = getTableName(classOfT);
        String countSql = "select count(1) from " + tableName + " " + queryModel.getNoOrderQueryStr();

        int count = getCount(countSql, queryModel.getParams());

        pageBean.setTotalCount(count);

        int startRowNum = pageBean.getCurrentPoint() - 1;

        String pageBeanSql = "select " + queryModel.getSelectFields() + " from " + tableName + " " + queryModel.getOrderQueryStr()
                + " limit " + startRowNum + "," + pageSize;

        List<T> list = getList(classOfT, pageBeanSql, queryModel.getParams());
        pageBean.setResultList(list);
        return pageBean;
    }

    public List<Map<String, Object>> getSqlMapList(String sql, Object... params) {
        return this.getMapList(sql, params);
    }

    public boolean save(Map<String, Object> map, String tableName) throws SQLException {
        String fields = "";
        String values = "";
        Object[] params = new Object[map.size()];
        int i = 0;
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String key = entry.getKey();
            if (i == 0) {
                fields = key;
                values = "?";
            } else {
                fields += "," + key;
                values += "," + "?";
            }
            params[i] = entry.getValue();
            i++;
        }
        String sql = "insert into " + tableName + " (" + fields + ") values (" + values + ")";
        try {
            Connection connection = getCon();
            if (connection.getAutoCommit()) {
                try {
                    return QRY_RUN.update(connection, sql, params) > 0;
                } finally {
                    closeCon(connection);
                }
            } else {
                return QRY_RUN.update(connection, sql, params) > 0;
            }
        } catch (Exception e) {
            setException(e);
            log.error("修改出错,sql:" + sql, e);
            return false;
        }
    }

    /**
     * 
     * 
     * @Title updateSql
     * @date 2018年6月13日 上午8:49:49
     * @param map
     *            update set 要修改的值
     * @param tableName
     * @param searchMap
     *            where 后的条件
     * @return
     * @throws SQLException
     *             boolean
     */
    public boolean updateSql(Map<String, Object> map, String tableName, Map<String, Object> searchMap) throws SQLException {
        StringBuilder valuesSb = new StringBuilder("");
        Object[] params = new Object[map.size()];
        int i = 0;
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String key = entry.getKey();
            if (i == 0) {
                valuesSb.append(key).append("=?");
            } else {
                valuesSb.append(", ").append(key).append("=?");
            }
            params[i] = entry.getValue();
            i++;
        }
        int j = 0;
        StringBuilder where = new StringBuilder("");
        for (Map.Entry<String, Object> entry : searchMap.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            if (j == 0) {
                where.append(key).append("='").append(value).append("'  ");
            } else {
                where.append(" and ").append(key).append("='").append(value).append("'  ");
            }
            j++;
        }
        String sql = "update " + tableName + " set " + valuesSb.toString() + " where " + where;
        try {
            Connection connection = getCon();
            if (connection.getAutoCommit()) {
                try {
                    return QRY_RUN.update(connection, sql, params) > 0 ? true : false;
                } finally {
                    closeCon(connection);
                }
            } else {
                return QRY_RUN.update(connection, sql, params) > 0 ? true : false;
            }
        } catch (Exception e) {
            setException(e);
            log.error("修改出错,sql:" + sql, e);
            return false;
        }
    }

    /**
     * 覆盖父类方法，将异常信息写到事务管理器中
     * 
     * @Title setException
     * @author 吕凯
     * @date 2019年3月29日 下午5:27:36
     * @param e
     * @see com.ld.dao.BasicDao#setException(java.lang.Exception)
     */
    @Override
    public void setException(Exception e) {
        super.setException(e);
        TransactionManager.saveException(DATA_SOURCE, e);
    }

    private Connection getConnection() throws Exception {
        Connection connection = CONNECTION_THREAD_LOCAL.get();
        if (connection != null && !connection.isClosed()) {
            return connection;
        } else {
            // connection = DATA_SOURCE.getConnection();
            connection = TransactionManager.getCurrentThreadConnection(DATA_SOURCE);// 改为从事务管理器中获取
            CONNECTION_THREAD_LOCAL.set(connection);
            return connection;
        }
    }

    private void closeConnection(Connection con) throws Exception {
        con.close();
        CONNECTION_THREAD_LOCAL.remove();
        exceptionThreadLocal.remove();
        TransactionManager.closeConn(DATA_SOURCE); // 事务管理器中关闭连接
    }

    @Override
    public Connection getCon() throws Exception {
        return getConnection();
    }

    @Override
    public void closeCon(Connection con) throws Exception {
        closeConnection(con);
    }

    @Override
    public int update(String sql, Object... params) {
        try {
            Connection connection = getCon();
            if (connection.getAutoCommit()) {
                try {
                    return QRY_RUN.update(connection, sql, params);
                } finally {
                    closeCon(connection);
                }
            } else {
                return QRY_RUN.update(connection, sql, params);
            }
        } catch (Exception e) {
            setException(e);
            log.error("修改出错,sql:" + sql, e);
            return 0;
        }
    }

    /**
     * 执行语句
     * 
     * @Title execute
     * @author 吕凯
     * @date 2018年12月29日 上午8:26:33
     * @param sql
     * @param params
     * @return int
     */
    public int execute(String sql, Object... params) {
        return update(sql, params);
    }

    public int deleteBySql(String sql) {
        try {
            Connection connection = getCon();
            if (connection.getAutoCommit()) {
                try {
                    return QRY_RUN.update(connection, sql);
                } finally {
                    closeCon(connection);
                }
            } else {
                return QRY_RUN.update(connection, sql);
            }
        } catch (Exception e) {
            setException(e);
            log.error("修改出错,sql:" + sql, e);
            return -1;
        }
    }

    /**
     * 
     * 
     * @Title updateThrowEx
     * @author 于国帅
     * @date 2018年4月11日 上午9:24:08
     * @param sql
     * @param params
     * @return int
     */
    public int updateThrowEx(String sql, Object... params) throws Exception {
        Connection connection = getCon();
        if (connection.getAutoCommit()) {
            try {
                return QRY_RUN.update(connection, sql, params);
            } finally {
                closeCon(connection);
            }
        } else {
            return QRY_RUN.update(connection, sql, params);
        }
    }

    private int getTotal(int[] counts) {
        if (counts == null || counts.length == 0) {
            return 0;
        }
        int total = 0;
        for (int count : counts) {
            if (count != 0) {
                total++;
            }
        }
        return total;
    }

    @Override
    public int batchUpdate(String sql, Object[][] params) {
        try {
            Connection connection = getCon();
            if (connection.getAutoCommit()) {
                try {
                    int[] counts = QRY_RUN.batch(connection, sql, params);
                    return getTotal(counts);
                } finally {
                    closeCon(connection);
                }
            } else {
                int[] counts = QRY_RUN.batch(connection, sql, params);
                return getTotal(counts);
            }

        } catch (Exception e) {
            setException(e);
            log.error("批量更新修改出错,sql:" + sql, e);
            return 0;
        }
    }

    /**
     * 批量执行
     * 
     * @Title batchExecute
     * @author 吕凯
     * @date 2018年12月29日 上午8:27:31
     * @param sql
     * @param params
     * @return int
     */
    public int batchExecute(String sql, Object[][] params) {
        return batchUpdate(sql, params);
    }

    public int getCountSql(String sql, Object... params) {
        return this.getCount(sql, params);
    }

    /**
     * 
     * 获取数据库事物异常
     * 
     * @Title getException
     * @return
     * @throws SQLException
     *             Exception
     */
    @Override
    public Exception getException() {
        Exception exception = exceptionThreadLocal.get();
        if (exception != null) {
            return exception;
        }
        return null;
    }

    /**
     * 
     * 是否存在异常
     * 
     * @Title hasException
     * @return
     * @throws SQLException
     *             boolean
     */
    @Override
    public boolean hasException() {
        return getException() != null;
    }

    /**
     * 
     * 开始事务
     * 
     * @Title starTransaction
     * @throws SQLException
     *             void
     */
    public void starTransaction() throws Exception {
        Connection con = getConnection();
        con.setAutoCommit(false);
    }

    /**
     * 
     * 提交事务（建议使用TransactionManager进行事务管理）
     * 
     * @Title commitTransaction
     * @throws Exception
     *             void
     */
    @Deprecated
    public void commitTransaction() throws Exception {
        Connection con = CONNECTION_THREAD_LOCAL.get();
        Exception excep = exceptionThreadLocal.get();
        if (excep == null) {
            if (con != null) {
                con.commit();
                con.setAutoCommit(true);
                closeConnection(con);
            } else {
                String msg = StackTraceUtil.getTrace();
                log.error("事务提交失败，连接为空！" + msg);
            }
        } else {
            log.error("事务提交失败，存在异常,进行回滚！");
            throw new Exception(excep);
        }
    }

    /**
     * 
     * 回滚事务（建议使用TransactionManager进行事务管理）
     * 
     * @Title rollbackTransaction
     * @return boolean
     */
    @Deprecated
    public boolean rollbackTransaction() {
        Connection con = CONNECTION_THREAD_LOCAL.get();
        if (con != null) {
            try {
                log.info("回滚事务");
                con.rollback();
                con.setAutoCommit(true);
                closeConnection(con);
                return true;
            } catch (Exception e) {
                log.error("事务回滚失败！", e);
            }
        } else {
            log.error("事务回滚失败，连接为空！");
        }
        return false;
    }

    @Override
    protected <T> void updateIndexField(T modelBean, Field field, Map<String, Field> fileMap)
            throws IllegalArgumentException, IllegalAccessException {
        // 未实现
    }

    @Override
    protected <T> void updateIndexFieldContrast(T modelBean, T oldmodelBean, Map<String, Field> fieldMap) {
        // 未实现
    }

    @Override
    protected <T> long getNextIdLong(Class<T> classOfT) {
        return 0;
    }

    @Override
    protected <T> long getNextSeqValue(Class<T> classOfT, String fieldName) {
        return 0;
    }

}
